﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using Waker.Entitys;
using Waker.Common;
using System.Data.SqlClient;

namespace Waker.DAL
{
    public class DAL_Base
    {
        public readonly Database Db;
        public DAL_Base()
        {
            Db = DatabaseFactory.CreateDatabase();
        }
        //public DataTable GetTableByTableName(string tableName)
        //{
        //    if (string.IsNullOrEmpty(tableName))
        //        return null;
        //    var strSql = string.Format("SELECT * FROM {0}", tableName);
        //    DbCommand commnd = Db.GetSqlStringCommand(strSql);
        //    DataSet ds = Db.ExecuteDataSet(commnd);
        //    if (ds == null)
        //        return null;
        //    return ds.Tables[0];
        //}
        /// <summary>
        /// 获取表结果
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataTable GetTableBySql(string strSql, List<En_Param> parms)
        {
            DbCommand commnd = Db.GetSqlStringCommand(strSql);
            if (parms != null && parms.Count > 0)
            {
                foreach (En_Param parm in parms)
                {
                    Db.AddInParameter(commnd, parm.ParmName, parm.ParmType, parm.ParmValue);
                }
            }
            DataSet ds = Db.ExecuteDataSet(commnd);
            if (ds == null)
                return null;
            return ds.Tables[0];
        }
        /// <summary>
        /// 获取表结果
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataTable GetTableBySql(string strSql, params En_Param[] parms)
        {
            DbCommand commnd = Db.GetSqlStringCommand(strSql);
            if (parms != null && parms.Length > 0)
            {
                foreach (En_Param parm in parms)
                {
                    Db.AddInParameter(commnd, parm.ParmName, parm.ParmType, parm.ParmValue);
                }
            }
            DataSet ds = Db.ExecuteDataSet(commnd);
            if (ds == null)
                return null;
            return ds.Tables[0];
        }
        /// <summary>
        /// 获取表结果(存储过程)
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public DataTable GetTableByProc(string procName, List<En_Param> parms)
        {
            DbCommand cmd = Db.GetStoredProcCommand(procName);
            if (parms != null && parms.Count > 0)
            {
                foreach (En_Param parm in parms)
                {
                    Db.AddInParameter(cmd, parm.ParmName, parm.ParmType, parm.ParmValue);
                }
            }
            DataSet ds = Db.ExecuteDataSet(cmd);
            if (ds == null)
                return null;
            return ds.Tables[0];
        }
        /// <summary>
        /// 获取表结果(存储过程)
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public DataTable GetTableByProc(string procName, params En_Param[] parms)
        {
            DbCommand cmd = Db.GetStoredProcCommand(procName);
            if (parms != null && parms.Length > 0)
            {
                foreach (En_Param parm in parms)
                {
                    Db.AddInParameter(cmd, parm.ParmName, parm.ParmType, parm.ParmValue);
                }
            }
            DataSet ds = Db.ExecuteDataSet(cmd);
            if (ds == null)
                return null;
            return ds.Tables[0];
        }
        public DataSet GetDataSetByProc(string procName, List<En_Param> parms)
        {
            DbCommand cmd = Db.GetStoredProcCommand(procName);
            if (parms != null && parms.Count > 0)
            {
                foreach (En_Param parm in parms)
                {
                    Db.AddInParameter(cmd, parm.ParmName, parm.ParmType, parm.ParmValue);
                }
            }
            DataSet ds = Db.ExecuteDataSet(cmd);

            return ds;
        }
        public DataSet GetDataSetByProc(string procName, params En_Param[] parms)
        {
            DbCommand cmd = Db.GetStoredProcCommand(procName);
            if (parms != null && parms.Length > 0)
            {
                foreach (En_Param parm in parms)
                {
                    Db.AddInParameter(cmd, parm.ParmName, parm.ParmType, parm.ParmValue);
                }
            }
            DataSet ds = Db.ExecuteDataSet(cmd);

            return ds;
        }
        public DataSet GetDataSetBySql(string sql, params En_Param[] parms)
        {
            DbCommand cmd = Db.GetSqlStringCommand(sql);
            if (parms != null && parms.Length > 0)
            {
                foreach (En_Param parm in parms)
                {
                    Db.AddInParameter(cmd, parm.ParmName, parm.ParmType, parm.ParmValue);
                }
            }
            DataSet ds = Db.ExecuteDataSet(cmd);

            return ds;
        }
        /// <summary>
        /// 获取对象结果
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public object GetObject(string strSql)
        {
            DbCommand cmd = Db.GetSqlStringCommand(strSql);
            return Db.ExecuteScalar(cmd);
        }
        /// <summary>
        /// 获取对象结果
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public object GetObject(string strSql, params En_Param[] parms)
        {
            DbCommand cmd = Db.GetSqlStringCommand(strSql);
            if (parms != null && parms.Length > 0)
            {
                foreach (En_Param parm in parms)
                {
                    Db.AddInParameter(cmd, parm.ParmName, parm.ParmType, parm.ParmValue);
                }
            }
            return Db.ExecuteScalar(cmd);
        }
        /// <summary>
        /// 获取对象结果
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public object GetObject(string strSql, List<En_Param> parms)
        {
            DbCommand cmd = Db.GetSqlStringCommand(strSql);
            if (parms != null && parms.Count > 0)
            {
                foreach (En_Param parm in parms)
                {
                    Db.AddInParameter(cmd, parm.ParmName, parm.ParmType, parm.ParmValue);
                }
            }
            return Db.ExecuteScalar(cmd);
        }
        /// <summary>
        /// 执行非查询SQL
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public int ExecuteNonQuerySql(string strSql, List<En_Param> parms)
        {
            DbCommand cmd = Db.GetSqlStringCommand(strSql);
            if (parms != null && parms.Count > 0)
            {
                foreach (En_Param parm in parms)
                {
                    Db.AddInParameter(cmd, parm.ParmName, parm.ParmType, parm.ParmValue);
                }
            }
            return Db.ExecuteNonQuery(cmd);
        }
        public int ExecuteNonQuerySql(string strSql, params En_Param[] parms)
        {
            DbCommand cmd = Db.GetSqlStringCommand(strSql);
            if (parms != null && parms.Length > 0)
            {
                foreach (En_Param parm in parms)
                {
                    Db.AddInParameter(cmd, parm.ParmName, parm.ParmType, parm.ParmValue);
                }
            }
            return Db.ExecuteNonQuery(cmd);
        }
        /// <summary>
        /// 批量执行sql(有事务)
        /// </summary>
        /// <param name="listCmd"></param>
        /// <returns></returns>
        public bool ExecuteNonQuerySqlOnTrans(List<DbCommand> listCmd)
        {
            if (listCmd.Count < 1)
                return false;
            using (DbConnection conn = Db.CreateConnection())
            {
                conn.Open();
                DbTransaction tran = conn.BeginTransaction();
                try
                {
                    foreach (var cmd in listCmd)
                    {
                        var res = Db.ExecuteNonQuery(cmd);
                        if (res < 0)
                        {
                            tran.Rollback();
                            return false;
                        }
                    };
                    tran.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
        }
        public bool ExecuteNonQuerySqlOnTrans(params DbCommand[] listCmd)
        {
            if (listCmd.Length < 1)
                return false;
            using (DbConnection conn = Db.CreateConnection())
            {
                conn.Open();
                DbTransaction tran = conn.BeginTransaction();
                try
                {
                    foreach (var cmd in listCmd)
                    {
                        var res = Db.ExecuteNonQuery(cmd);
                        if (res < 0)
                        {
                            tran.Rollback();
                            return false;
                        }
                    };
                    tran.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
        }
        /// <summary>
        /// 执行非查询存储过程
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public int ExecuteNonQueryProc(string procName, List<En_Param> parms)
        {
            DbCommand cmd = Db.GetStoredProcCommand(procName);
            if (parms != null && parms.Count > 0)
            {
                foreach (En_Param parm in parms)
                {
                    Db.AddInParameter(cmd, parm.ParmName, parm.ParmType, parm.ParmValue);
                }
            }
            return Db.ExecuteNonQuery(cmd);
        }
        public int ExecuteNonQueryProc(string procName, params En_Param[] parms)
        {
            DbCommand cmd = Db.GetStoredProcCommand(procName);
            if (parms != null && parms.Length > 0)
            {
                foreach (En_Param parm in parms)
                {
                    Db.AddInParameter(cmd, parm.ParmName, parm.ParmType, parm.ParmValue);
                }
            }
            return Db.ExecuteNonQuery(cmd);
        }
        public int UpdateDataTableBySql(string strInsert, string strUpdate, string strDelete, DataTable table)
        {
            DbCommand cmdInsert = null;
            if (!string.IsNullOrEmpty(strInsert))
                cmdInsert = Db.GetSqlStringCommand(strInsert);

            DbCommand cmdDelete = null;
            if (!string.IsNullOrEmpty(strDelete))
                cmdDelete = Db.GetSqlStringCommand(strDelete);

            DbCommand cmdUpdate = null;
            if (!string.IsNullOrEmpty(strUpdate))
                cmdUpdate = Db.GetSqlStringCommand(strUpdate);

            if (table.DataSet == null)
            {
                DataSet ds = new DataSet();
                ds.Tables.Add(table);
            }
            foreach (DataColumn col in table.Columns)
            {
                var dbtyp = DbTypeHelper.TypeToDbType(col.DataType);
                if (cmdInsert != null)
                    Db.AddInParameter(cmdInsert, col.ColumnName, dbtyp, col.ColumnName, DataRowVersion.Current);
                if (cmdUpdate != null)
                    Db.AddInParameter(cmdUpdate, col.ColumnName, dbtyp, col.ColumnName, DataRowVersion.Current);
                if (cmdDelete != null)
                    Db.AddInParameter(cmdDelete, col.ColumnName, dbtyp, col.ColumnName, DataRowVersion.Current);
            }
            int rowsAffected = Db.UpdateDataSet(table.DataSet, table.TableName, cmdInsert, cmdUpdate, cmdDelete, UpdateBehavior.Transactional);
            return rowsAffected;
        }
        public int UpdateDataSetByProc(string procName, DataTable table, List<En_Param> parms)
        {
            DbCommand cmd = Db.GetStoredProcCommand(procName);

            if (table.DataSet == null)
            {
                DataSet ds = new DataSet();
                ds.Tables.Add(table);
            }
            if (parms != null && parms.Count > 0)
            {
                foreach (En_Param parm in parms)
                {
                    Db.AddInParameter(cmd, parm.ParmName, parm.ParmType, parm.ParmValue.ToString(), DataRowVersion.Current);
                }
            }
            return Db.UpdateDataSet(table.DataSet, table.TableName, cmd, cmd, null, UpdateBehavior.Transactional);
        }

        public int UpdateDataTable(DataTable dt)
        {
            int res = -1;
            using (SqlConnection conn = (SqlConnection)Db.CreateConnection())
            {
                SqlDataAdapter myAdapter = new SqlDataAdapter();
                SqlCommand myCommand = new SqlCommand("select * from " + dt.TableName, conn);
                myAdapter.SelectCommand = myCommand;
                SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
                try
                {

                    lock (this)            //处理并发情况(分布式情况)
                    {
                        res = myAdapter.Update(dt);
                    }

                }

                catch (Exception err)
                {
                    throw err;
                }
                finally
                {
                    myCommandBuilder.Dispose();
                    dt.Dispose();
                    myAdapter.Dispose();
                    conn.Close();
                }
            }
            return res;
        }
        public int UpdateDataTable1(DataTable dt)
        {
            int res = -1;
            using (SqlConnection conn = (SqlConnection)Db.CreateConnection())
            {
                SqlCommand myCommand = new SqlCommand("select * from " + dt.TableName + " where 1=2", conn);

                SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);

                SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);

                myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();

                myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand();

                myAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand();

                try
                {
                    lock (this)                              //处理并发情况(分布式情况) 
                    {
                        conn.Open();
                        myAdapter.Update(dt);
                    }
                }
                catch (Exception err)
                {
                    throw err;
                }
                finally
                {
                    myCommandBuilder.Dispose();
                    dt.Dispose();
                    myAdapter.Dispose();
                    conn.Close();
                }
                return res;
            }
        }
        public int GetNextId(string tableName, string colName)
        {
            var sql = "SELECT ISNULL((SELECT MAX(ISNULL(" + colName + ",0)) FROM " + tableName + "),0)";
            return Convert.ToInt32(GetObject(sql)) + 1;
        }
    }
}
